package com.sl.au.repository;
import java.util.List;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;
import com.sl.au.entity.QuoteDevice;
import com.sl.au.entity.QuoteReplacement;

@Transactional(readOnly = true)
public interface QuoteDeviceRepository extends JpaRepository<QuoteDevice, String >{
	/**
	 * 根据外贸价格单Id得到泵细目 quoteDevice page
	 * @param quoteForeignId 外贸价格单Id
	 * @param pageable
	 * @return
	 */
	@Query(" select q from QuoteDevice q where (q.inventoryState!=1 or q.inventoryState is null) and q.quoteForeign.id=?1 ")
	Page<QuoteDevice> findQuoteDevice(String quoteForignId, Pageable pageable);
    /**
     * 根据外贸价格单Id和设备类型得到数据条数
     * @param quoteForeignId  外贸价格单Id
     */
	@Query(" select count(*) from QuoteDevice q where (q.inventoryState!=1 or q.inventoryState is null) and q.quoteForeign.id=?1")
	Long findQuoteDeviceCount(String quoteForeignId);
	
	/**
	 * 根据Id得到设备细目
	 * @param nowDataId quoteDevice数据的id
	 * @param pageable
	 * @return
	 */
	@Query(" select q from QuoteDevice q where q.id=?1")
	Page<QuoteDevice> getNowQuoteDevice(String nowDataId, Pageable pageable);
	/**
	 * 根据Id得到数据条数
	 * @param nowDataId quoteDevice数据id
	 */
	@Query(" select count(*) from QuoteDevice q where q.id=?1")
	long getNowDeviceCount(String nowDataId);
	
	/**
	 * 根据quoteForeign得到QuoteDevice List
	 * @param quoteForeignId 细目关联的外贸价格单id
	 * @return
	 */
	@Query(" select q from QuoteDevice q where q.quoteForeign.id=?1 and q.linkDeviceId is null")
	List<QuoteDevice> findQuoteDeviceByQuoteForeignId(String quoteForeignId);
	
	
	/**
	 * 根据普通价格单id得到设备细目 List
	 * @param quoteId 普通价格Id
	 * @return
	 */
	@Query(" select q from QuoteDevice q where q.quote.id=?1 and q.linkDeviceId is null")
	List<QuoteDevice> findQuoteDeviceByQuoteId(String quoteId);
	
	/**
	 * 得到对外普通价格单的主机细目
	 * @param quoteId 普通价格单Id
	 * @return
	 */
	@Query(" select q from QuoteDevice q where q.linkDeviceId is null and q.quote.id=?1 order by createTime desc")
	List<QuoteDevice> findQuoteDeviceList(String quoteId);
	
	/**
	 * 得到对外外贸价格单的主机细目
	 * @param quoteId 外贸价格单Id
	 * @return
	 */
	@Query(" select q from QuoteDevice q where q.linkDeviceId is null and q.quoteForeign.id=?1 order by createTime desc ")
	List<QuoteDevice> findQuoteForeignDeviceList(String quoteForeignId);
	
	/**
	 * 普通价格单中得细目
	 * @param quoteId  普通价格单id 
	 * @param pageable
	 * @return
	 */
	@Query(" select q from QuoteDevice q where (q.inventoryState!=1 or q.inventoryState is null) and q.quote.id=?1 ")
	Page<QuoteDevice> findQuoteDeviceByQuoteId(String quoteId,Pageable pageable);
	
	/** 普通价格单泵细目条数
	 * @param quoteId 普通价格单id
	 * @return
	 */
	@Query(" select count(*) from QuoteDevice q where (q.inventoryState!=1 or q.inventoryState is null) and q.quote.id=?1")
	Long findDeviceInQuoteCount(String quoteId);
	
	@Query(" select q from QuoteDevice q where q.linkDeviceId=?1")
	List<QuoteDevice> findLinkDevice(String quoteDeviceId);
	
	/*
	 * 获得泵的历史报价
	 * 所有
	 */
	@Query(" select q from QuoteDevice q where (q.quote.id is not null) "
			+ "and (q.inventoryState=0 or q.inventoryState is null) "
			+ "and (q.productName like '%'+?1+'%' or q.productType like '%'+?1+'%' or q.subDivideType like '%'+?1+'%' "
			+ "or q.productModel like '%'+?1+'%' "
			+ "or q.quote.user.empLogin.userName like '%'+?1+'%')")
	Page<QuoteDevice> getQuoteDevice(String key,Pageable pageable);
	
	@Query(" select count(*) from QuoteDevice q where (q.quote.id is not null) "
			+ "and (q.inventoryState=0 or q.inventoryState is null) "
			+ "and (q.productName like '%'+?1+'%' or q.productType like '%'+?1+'%' or q.subDivideType like '%'+?1+'%' "
			+ "or q.productModel like '%'+?1+'%' "
			+ "or q.quote.user.empLogin.userName like '%'+?1+'%')")
	long getQuoteDeviceCount(String key);
	
	/////外贸处的
	@Query(" select q from QuoteDevice q where (q.quoteForeign.id is not null) "
			+ "and (q.inventoryState=0 or q.inventoryState is null) "
			+ "and (q.productName like '%'+?1+'%' or q.productType like '%'+?1+'%' or q.subDivideType like '%'+?1+'%' "
			+ "or q.productModel like '%'+?1+'%' "
			+ "or q.quoteForeign.user.empLogin.userName like '%'+?1+'%')")
	Page<QuoteDevice> getQuoteForeignDevice(String key,Pageable pageable);
	
	@Query(" select count(*) from QuoteDevice q where (q.quoteForeign.id is not null) "
			+ "and (q.inventoryState=0 or q.inventoryState is null) "
			+ "and (q.productName like '%'+?1+'%' or q.productType like '%'+?1+'%' or q.subDivideType like '%'+?1+'%' "
			+ "or q.productModel like '%'+?1+'%' "
			+ "or q.quoteForeign.user.empLogin.userName like '%'+?1+'%')")
	long getQuoteDeviceForeignCount(String key);

	
	/*
	 * 获得泵的历史报价
	 * 本部门
	 */
	@Query(" select q from QuoteDevice q where (q.quote.id is not null) "
			+ "and (q.inventoryState=0 or q.inventoryState is null) "
			+ "and (q.quote.user.saleArea.superior =?2) "
			+ "and (q.quote.auditState='通过')"
			+ "and (q.productName like '%'+?1+'%' or q.productType like '%'+?1+'%' or q.subDivideType like '%'+?1+'%' "
			+ "or q.productModel like '%'+?1+'%' "
			+ "or q.quote.user.empLogin.userName like '%'+?1+'%')")
	Page<QuoteDevice> getQuoteDevice(String key,String superior,Pageable pageable);
	
	@Query(" select count(*) from QuoteDevice q where (q.quote.id is not null) "
			+ "and (q.inventoryState=0 or q.inventoryState is null) "
			+ "and (q.quote.user.saleArea.superior =?2) "
			+ "and (q.quote.auditState='通过')"
			+ "and (q.productName like '%'+?1+'%' or q.productType like '%'+?1+'%' or q.subDivideType like '%'+?1+'%' "
			+ "or q.productModel like '%'+?1+'%' "
			+ "or q.quote.user.empLogin.userName like '%'+?1+'%')")
	long getQuoteDeviceCount(String key,String superior);
	
	@Query(" select q from QuoteDevice q where (q.quoteForeign.id is not null) "
			+ "and (q.inventoryState=0 or q.inventoryState is null) "
			+ "and (q.quoteForeign.user.saleArea.superior =?2) "
			+ "and (q.quoteForeign.auditState='通过')"
			+ "and (q.productName like '%'+?1+'%' or q.productType like '%'+?1+'%' or q.subDivideType like '%'+?1+'%' "
			+ "or q.productModel like '%'+?1+'%' "
			+ "or q.quoteForeign.user.empLogin.userName like '%'+?1+'%')")
    Page<QuoteDevice> getQuoteForeignDevice(String key,String superior,Pageable pageable);
	
	@Query(" select count(*) from QuoteDevice q where (q.quoteForeign.id is not null) "
			+ "and (q.inventoryState=0 or q.inventoryState is null) "
			+ "and (q.quoteForeign.user.saleArea.superior =?2) "
			+ "and (q.quoteForeign.auditState='通过')"
			+ "and (q.productName like '%'+?1+'%' or q.productType like '%'+?1+'%' or q.subDivideType like '%'+?1+'%' "
			+ "or q.productModel like '%'+?1+'%' "
			+ "or q.quoteForeign.user.empLogin.userName like '%'+?1+'%')")
	long getQuoteDeviceForeignCount(String key,String superior);
	
	
	/*
	 * 获得泵的历史报价
	 * 本片区
	 */
	@Query(" select q from QuoteDevice q where (q.quote.id is not null) "
			+ "and (q.inventoryState=0 or q.inventoryState is null) "
			+ "and (q.quote.user.saleArea.id =?2) "
			+ "and (q.quote.auditState='通过')"
			+ "and (q.productName like '%'+?1+'%' or q.productType like '%'+?1+'%' or q.subDivideType like '%'+?1+'%' "
			+ "or q.productModel like '%'+?1+'%' "
			+ "or q.quote.user.empLogin.userName like '%'+?1+'%')")
	Page<QuoteDevice> getQuoteAreaDevice(String key,String areaId,Pageable pageable);
	
	@Query(" select count(*) from QuoteDevice q where (q.quote.id is not null) "
			+ "and (q.inventoryState=0 or q.inventoryState is null) "
			+ "and (q.quote.user.saleArea.id =?2) "
			+ "and (q.quote.auditState='通过')"
			+ "and (q.productName like '%'+?1+'%' or q.productType like '%'+?1+'%' or q.subDivideType like '%'+?1+'%' "
			+ "or q.productModel like '%'+?1+'%' "
			+ "or q.quote.user.empLogin.userName like '%'+?1+'%')")
	long getQuoteDeviceAreaCount(String key,String areaId);
	
	@Query(" select q from QuoteDevice q where (q.quoteForeign.id is not null) "
			+ "and (q.inventoryState=0 or q.inventoryState is null) "
			+ "and (q.quoteForeign.user.saleArea.id =?2) "
			+ "and (q.quoteForeign.auditState='通过')"
			+ "and (q.productName like '%'+?1+'%' or q.productType like '%'+?1+'%' or q.subDivideType like '%'+?1+'%' "
			+ "or q.productModel like '%'+?1+'%' "
			+ "or q.quoteForeign.user.empLogin.userName like '%'+?1+'%')")
    Page<QuoteDevice> getQuoteForeignAreaDevice(String key,String areaId,Pageable pageable);
	
	@Query(" select count(*) from QuoteDevice q where (q.quoteForeign.id is not null) "
			+ "and (q.inventoryState=0 or q.inventoryState is null) "
			+ "and (q.quoteForeign.user.saleArea.id =?2) "
			+ "and (q.quoteForeign.auditState='通过')"
			+ "and (q.productName like '%'+?1+'%' or q.productType like '%'+?1+'%' or q.subDivideType like '%'+?1+'%' "
			+ "or q.productModel like '%'+?1+'%' "
			+ "or q.quoteForeign.user.empLogin.userName like '%'+?1+'%')")
	long getQuoteDeviceForeignAreaCount(String key,String areaId);
	
	
	/*
	 * 获得泵的历史报价
	 * 本人
	 */
	@Query(" select q from QuoteDevice q where (q.quote.id is not null) "
			+ "and (q.inventoryState=0 or q.inventoryState is null) "
			+ "and (q.quote.user.id =?2) "
			+ "and (q.quote.auditState='通过')"
			+ "and (q.productName like '%'+?1+'%' or q.productType like '%'+?1+'%' or q.subDivideType like '%'+?1+'%' "
			+ "or q.productModel like '%'+?1+'%' "
			+ "or q.quote.user.empLogin.userName like '%'+?1+'%')")
	Page<QuoteDevice> getQuoteSelfDevice(String key,String userId,Pageable pageable);
	
	@Query(" select count(*) from QuoteDevice q where (q.quote.id is not null) "
			+ "and (q.inventoryState=0 or q.inventoryState is null) "
			+ "and (q.quote.user.id =?2) "
			+ "and (q.quote.auditState='通过')"
			+ "and (q.productName like '%'+?1+'%' or q.productType like '%'+?1+'%' or q.subDivideType like '%'+?1+'%' "
			+ "or q.productModel like '%'+?1+'%' "
			+ "or q.quote.user.empLogin.userName like '%'+?1+'%')")
	long getQuoteDeviceSelfCount(String key,String userId);
	
	@Query(" select q from QuoteDevice q where (q.quoteForeign.id is not null) "
			+ "and (q.inventoryState=0 or q.inventoryState is null) "
			+ "and (q.quoteForeign.user.id =?2) "
			+ "and (q.quoteForeign.auditState='通过')"
			+ "and (q.productName like '%'+?1+'%' or q.productType like '%'+?1+'%' or q.subDivideType like '%'+?1+'%' "
			+ "or q.productModel like '%'+?1+'%' "
			+ "or q.quoteForeign.user.empLogin.userName like '%'+?1+'%')")
    Page<QuoteDevice> getQuoteForeignSelfDevice(String key,String userId,Pageable pageable);
	
	@Query(" select count(*) from QuoteDevice q where (q.quoteForeign.id is not null) "
			+ "and (q.inventoryState=0 or q.inventoryState is null) "
			+ "and (q.quoteForeign.user.id =?2) "
			+ "and (q.quoteForeign.auditState='通过')"
			+ "and (q.productName like '%'+?1+'%' or q.productType like '%'+?1+'%' or q.subDivideType like '%'+?1+'%' "
			+ "or q.productModel like '%'+?1+'%' "
			+ "or q.quoteForeign.user.empLogin.userName like '%'+?1+'%')")
	long getQuoteDeviceForeignSelfCount(String key,String userId);
	
	
	
	
	
	/*
	 * 获得备件的历史报价
	 * 所有
	 */
	@Query(" select q from QuoteReplacement q where q.deviceName like '%'+?1+'%' or q.material like '%'+?1+'%' "
			+ "or q.type like '%'+?1+'%' or q.quote.user.empLogin.userName like '%'+?1+'%'")
	Page<QuoteReplacement> getQuoteReplace(String key,Pageable pageable);
	
	@Query(" select count(*) from QuoteReplacement q where q.deviceName like '%'+?1+'%' or q.material like '%'+?1+'%' "
			+ "or q.type like '%'+?1+'%' or q.quote.user.empLogin.userName like '%'+?1+'%'")
	long getQuoteReplaceCount(String key);
	
	/*
	 * 获得备件的历史报价
	 * 本部门
	 */
	@Query(" select q from QuoteReplacement q where (q.quote.id is not null)"
			+ "and (q.deviceName like '%'+?1+'%' or q.material like '%'+?1+'%' "
			+ "or q.type like '%'+?1+'%' or q.quote.user.empLogin.userName like '%'+?1+'%') "
			+ "and (q.quote.user.saleArea.superior =?2) ")
	Page<QuoteReplacement> getQuoteReplace(String key,String superior,Pageable pageable);
	
	@Query(" select count(*) from QuoteReplacement q where (q.quote.id is not null)"
			+ "and (q.deviceName like '%'+?1+'%' or q.material like '%'+?1+'%' "
			+ "or q.type like '%'+?1+'%' or q.quote.user.empLogin.userName like '%'+?1+'%') "
			+ "and (q.quote.user.saleArea.superior =?2) ")
	long getQuoteReplaceCount(String key,String superior);
	
	@Query(" select q from QuoteReplacement q where (q.quoteForeign.id is not null)"
			+ "and (q.deviceName like '%'+?1+'%' or q.material like '%'+?1+'%' "
			+ "or q.type like '%'+?1+'%' or q.quoteForeign.user.empLogin.userName like '%'+?1+'%') "
			+ "and (q.quoteForeign.user.saleArea.superior =?2) ")
	Page<QuoteReplacement> getForeignReplace(String key,String superior,Pageable pageable);
	
	@Query(" select count(*) from QuoteReplacement q where (q.quoteForeign.id is not null)"
			+ "and (q.deviceName like '%'+?1+'%' or q.material like '%'+?1+'%' "
			+ "or q.type like '%'+?1+'%' or q.quoteForeign.user.empLogin.userName like '%'+?1+'%') "
			+ "and (q.quoteForeign.user.saleArea.superior =?2) ")
	long getForeignReplaceCount(String key,String superior);
	
	/*
	 * 获得备件的历史报价
	 * 本片区
	 */
	@Query(" select q from QuoteReplacement q where (q.quote.id is not null)"
			+ "and (q.deviceName like '%'+?1+'%' or q.material like '%'+?1+'%' "
			+ "or q.type like '%'+?1+'%' or q.quote.user.empLogin.userName like '%'+?1+'%') "
			+ "and (q.quote.user.saleArea.id =?2) ")
	Page<QuoteReplacement> getQuoteAreaReplace(String key,String areaId,Pageable pageable);
	
	@Query(" select count(*) from QuoteReplacement q where (q.quote.id is not null)"
			+ "and (q.deviceName like '%'+?1+'%' or q.material like '%'+?1+'%' "
			+ "or q.type like '%'+?1+'%' or q.quote.user.empLogin.userName like '%'+?1+'%') "
			+ "and (q.quote.user.saleArea.id =?2) ")
	long getQuoteReplaceAreaCount(String key,String areaId);
	
	@Query(" select q from QuoteReplacement q where (q.quoteForeign.id is not null)"
			+ "and (q.deviceName like '%'+?1+'%' or q.material like '%'+?1+'%' "
			+ "or q.type like '%'+?1+'%' or q.quoteForeign.user.empLogin.userName like '%'+?1+'%') "
			+ "and (q.quoteForeign.user.saleArea.id =?2) ")
	Page<QuoteReplacement> getForeignAreaReplace(String key,String areaId,Pageable pageable);
	
	@Query(" select count(*) from QuoteReplacement q where (q.quoteForeign.id is not null)"
			+ "and (q.deviceName like '%'+?1+'%' or q.material like '%'+?1+'%' "
			+ "or q.type like '%'+?1+'%' or q.quoteForeign.user.empLogin.userName like '%'+?1+'%') "
			+ "and (q.quoteForeign.user.saleArea.id =?2) ")
	long getForeignReplaceAreaCount(String key,String areaId);
	
	/*
	 * 获得备件的历史报价
	 * 本人
	 */
	@Query(" select q from QuoteReplacement q where (q.quote.id is not null)"
			+ "and (q.deviceName like '%'+?1+'%' or q.material like '%'+?1+'%' "
			+ "or q.type like '%'+?1+'%' or q.quote.user.empLogin.userName like '%'+?1+'%') "
			+ "and (q.quote.user.id =?2) ")
	Page<QuoteReplacement> getQuoteSelfReplace(String key,String userId,Pageable pageable);
	
	@Query(" select count(*) from QuoteReplacement q where (q.quote.id is not null)"
			+ "and (q.deviceName like '%'+?1+'%' or q.material like '%'+?1+'%' "
			+ "or q.type like '%'+?1+'%' or q.quote.user.empLogin.userName like '%'+?1+'%') "
			+ "and (q.quote.user.id =?2) ")
	long getQuoteReplaceSelfCount(String key,String userId);
	
	@Query(" select q from QuoteReplacement q where (q.quoteForeign.id is not null)"
			+ "and (q.deviceName like '%'+?1+'%' or q.material like '%'+?1+'%' "
			+ "or q.type like '%'+?1+'%' or q.quoteForeign.user.empLogin.userName like '%'+?1+'%') "
			+ "and (q.quoteForeign.user.id =?2) ")
	Page<QuoteReplacement> getForeignSelfReplace(String key,String userId,Pageable pageable);
	
	@Query(" select count(*) from QuoteReplacement q where (q.quoteForeign.id is not null)"
			+ "and (q.deviceName like '%'+?1+'%' or q.material like '%'+?1+'%' "
			+ "or q.type like '%'+?1+'%' or q.quoteForeign.user.empLogin.userName like '%'+?1+'%') "
			+ "and (q.quoteForeign.user.id =?2) ")
	long getForeignReplaceSelfCount(String key,String userId);

}
